package com.amado.course45mysql02.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.amado.course45mysql02.domain.Student;
/**
 * #一页3条,123
参数1：(页码-1)*3，参数2：3
#参数1：(1-1)*3，参数2：3
select * from goods limit 0,3;
#一页3条,456
#参数1：(2-1)*3，参数2：3
select * from goods limit 3,3;
#一页3条,789
#参数1：(3-1)*3，参数2：3
select * from goods limit 6,3;

select * from goods limit 9,3;


#1，3
#页码
int page = count/3;
#是否有多余的数据
page = page + total%3==0?0:1

select count(1) from goods;

select * from t_student;

 * @author Administrator
 *
 */
public class EmployeePageDao {
//	查询得到一个对象
	public int count() {
		String sql = "select count(*) from t_student";
		Statement ps = null;
		ResultSet rs = null;
		Connection conn = DBConnection.getConn();
		try {
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.close(rs, ps, conn);
		return 0;
	}
//	查询得到一组数据
	/**
	 *  分页查询
	 * @param pageNumber 页码
	 * @param pageSize 一页有多少条数据
	 * @return LIST数据
	 */
	public List<Student> limit(int pageNumber,int pageSize) {
		String sql = "select * from t_student limit ?,?";
		Connection conn = DBConnection.getConn();
		List<Student> list = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			//参数1：(页码-1)*pageSize，参数2：pageSize
			ps.setInt(1, (pageNumber-1)*pageSize);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			//rs结果集
			while (rs.next()) {
				Student student = new Student();
				student.setId(rs.getLong(1));
				student.setName(rs.getString(2));
				student.setSex(rs.getByte(3));
				student.setPhone(rs.getString(4));
				student.setAge(rs.getShort(5));
				student.setCreate_time(rs.getDate(6));
				//循环添加到List
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.close(rs, ps, conn);
		return list;
	}
}
